package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.ChargeMin;
@Repository("chargeMinDao")
public class ChargeMinDaoImpl extends TemplateDaoImpl<ChargeMin, Integer>implements ChargeMinDao{
	/**
	 * 获取最后一条生产线或者设备半小时电费记录
	 * @param id  生产线或者设备id
	 * @param isLine  是否为生产线  Y为生产线，N为设备
	 * @return
	 */
	@Override
	public ChargeMin getLastChargeMin(int id,String isLine) {
		StringBuilder sb=new StringBuilder("select top 1 * from chargeMin where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append(" order by time desc ");
//		String sql="select top 1 * from chargeMin where equId=:equId order by time desc ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		return jt.queryForObject(sb.toString(), params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeMin.class));
	}
	/**
	 * 根据生产线或者设备id和时间查询半小时电费记录
	 * @param id  生产线或者设备id
	 * @param belongTime
	 * @param isLine  是否为生产线  Y为生产线，N为设备
	 * @return
	 */
	@Override
	public ChargeMin getChargeMinByIdAndDate(int id, String belongTime,String isLine) {
		StringBuilder sb=new StringBuilder("select * from chargeMin where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append(" and CONVERT(VARCHAR(19),belongTime,120)=:belongTime");
//		String  sql="select * from chargeMin where equId=:equId and CONVERT(VARCHAR(19),belongTime,120)=:belongTime";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("belongTime", belongTime);
		return jt.queryForObject(sb.toString(), params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeMin.class));
	}
	/**
	 * 根据开始时间和结束时间查询时间段内电费记录
	 * @param id   设备或者生产线id
	 * @param startDate
	 * @param endDate
	 * @param chargeName
	 * @param isLine
	 * @return
	 */
	@Override
	public List<ChargeMin> getChargeMinsByDate(int id,String startDate, String endDate,String chargeName,String isLine) {
		StringBuilder sb=new StringBuilder("select * from chargeMin where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append("  and CONVERT(VARCHAR(19),belongTime,120)>=:startDate and CONVERT(VARCHAR(19),belongTime,120)<=:endDate and chargeName=:chargeName ");
//		String  sql="select * from chargeMin where equId=:equId and CONVERT(VARCHAR(19),belongTime,120)>=:startDate and CONVERT(VARCHAR(19),belongTime,120)<=:endDate and chargeName=:chargeName ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		params.put("chargeName", chargeName);
		return jt.query(sb.toString(), params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeMin.class));
	}
	/**
	 * 根据时间区间查询半小时电费记录
	 * @param id设备id或者生产线id
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Override
	public List<ChargeMin> getChargesMinsByAearDate(int id, String startDate, String endDate,String isLine) {
//		String  sql="select * from chargeMin where equId=:equId and CONVERT(VARCHAR(19),belongTime,120)>=:startDate and CONVERT(VARCHAR(19),belongTime,120)<=:endDate  ";
		StringBuilder sb=new StringBuilder("select * from chargeMin where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append("  and CONVERT(VARCHAR(19),belongTime,120)>=:startDate and CONVERT(VARCHAR(19),belongTime,120)<=:endDate");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.query(sb.toString(), params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeMin.class));
	}
	
	/**
	 * 获取当天的所有半小时电度电费记录
	 * @param id设备或者生产线id
	 * @param belongTime(如：2016-06-16)
	 * @return
	 */
	@Override
	public List<ChargeMin> getChargeMinsByDay(int id, String belongTime,String isLine) {
//		String  sql="select * from chargeMin where equId=:equId and SUBSTRING(Convert(varchar(10),belongTime,121),1,10)=:belongTime";
		StringBuilder sb=new StringBuilder("select * from chargeMin where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append(" and Convert(varchar(10),belongTime,120)=:belongTime");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("belongTime", belongTime);
		return jt.query(sb.toString(), params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeMin.class));
	}
	/**
	 * 获取时间区间内消耗的电度
	 * @param equId
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Override
	public ChargeMin getDurationByTime(int equId, String startDate,
			String endDate) {
		String  sql="select COALESCE(SUM(relativeComp),0) relativeComp ,COALESCE(SUM(charge),0) charge from chargeMin where equId=:equId and CONVERT(VARCHAR(19),belongTime,120)>=:startDate and CONVERT(VARCHAR(19),belongTime,120)<=:endDate ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.queryForObject(sql.toString(), params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeMin.class));
	}
	/**
	 * 根据日期获取指定类型的电度电费累加值
	 * @param equId
	 * @param chargeName
	 * @param time(如：2016-06-16)
	 * @return
	 */
	@Override
	public ChargeMin getDurationListByType(int equId,String chargeName,String time) {
		String  sql="SELECT COALESCE(SUM(relativeComp),0) relativeComp ,COALESCE(SUM(charge),0) charge FROM chargeMin where equId=:equId and chargeName=:chargeName and SUBSTRING(Convert(varchar(10),belongTime,121),1,10)=:time";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("chargeName", chargeName);
		params.put("time", time);
		return jt.queryForObject(sql, params, 
				ParameterizedBeanPropertyRowMapper.newInstance(ChargeMin.class));
	}
}
